﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Domain;
using System.Data;

namespace Repository
{
    public class EnderecoDB : IQuery<Endereco>
    {
        private SqlConnection conn;

        public EnderecoDB(SqlConnection conn)
        {
            this.conn = conn;
        }




        public bool save(Endereco e)
        {
            try 
            {
                SqlCommand command = new SqlCommand("insert into endereco (id_cliente,endereco,numero,referencia,complemento,cep) values ("+e.Id_cliente+",'"+e.ENdereco+"',"+e.Numero+",'"+e.Referencia+"','"+e.Complemento+"','"+e.Cep+"')", this.conn);
                command.ExecuteNonQuery(); 
                return true;
            }catch(SqlException sqlex)
            {
                return false;
            }
        }

        public bool saveEnderecos(Cliente e)
        {
            List<Endereco> end = new List<Endereco>();
            end = e.Endereco;

            try
            {
                for (int i = 0; i < end.Count; i++)
                {
                    SqlCommand command = new SqlCommand("insert into endereco (id_cliente,endereco,numero,referencia,complemento,cep,ativo) values (" + e.Id_cliente + ",'" + end[i].ENdereco + "'," + end[i].Numero + ",'" + end[i].Referencia + "','" + end[i].Complemento + "','" + end[i].Cep + "','true')", this.conn);
                    command.ExecuteNonQuery();
                }
                return true;
            }
            catch (SqlException sqlex)
            {
                return false;
            }
        }

        public bool update(Endereco e)
        {
            try
            {
                SqlCommand command = new SqlCommand("update cliente set endereci= '" + e.ENdereco + "',referencia='" + e.Referencia + "',complemento='" + e.Complemento + "',numero=" + e.Numero + "", this.conn);
                command.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqlex)
            {
                return false;
            }
        }

        public List<Endereco> getAllList()
        {
            try
            {
                SqlCommand command = new SqlCommand("select * from endereco", this.conn);
                SqlDataReader dr = command.ExecuteReader();
                List<Endereco> enderecos = new List<Endereco>();

                while (dr.Read())
                {
                    Endereco p = new Endereco();
                    p.Id_cliente = Convert.ToInt32(dr["id_cliente"]);
                    p.ENdereco = Convert.ToString(dr["endereco"]);
                    p.Numero = Convert.ToInt32(dr["numero"]);
                    p.Referencia = Convert.ToString(dr["referencia"]);
                    p.Cep = Convert.ToString(dr["cep"]);
                    p.Complemento = Convert.ToString(dr["complemento"]);
                    enderecos.Add(p);
                }
                return enderecos;
            }
            catch (SqlException sqlex)
            {
                return null;
            }
        }
        //Testes para o funcionamento do datagridview
        public DataTable AuxEnderecoGrid() 
        {
            try
            {
                SqlCommand command = new SqlCommand("select * from endereco ", this.conn);
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (SqlException sqlex) 
            {
                return null;
            }
        }
        //Teste para o funcionamneto do datagridview
        public void DataGridEndereco() 
        {
            try 
            {
               
            }catch(SqlException sqlex)
            {

            }

        }

        public List<Endereco> getListById(int id)
        {
            try
            {
                SqlCommand command = new SqlCommand("select * from endereco where id_cliente = "+id+" ", this.conn);
                SqlDataReader dr = command.ExecuteReader();
                List<Endereco> enderecos = new List<Endereco>();

                while (dr.Read())
                {
                    Endereco p = new Endereco();
                    p.Id_cliente = Convert.ToInt32(dr["id_cliente"]);
                    p.ENdereco = Convert.ToString(dr["endereco"]);
                    p.Numero = Convert.ToInt32(dr["numero"]);
                    p.Referencia = Convert.ToString(dr["referencia"]);
                    p.Cep = Convert.ToString(dr["cep"]);
                    p.Complemento = Convert.ToString(dr["complemento"]);

                    enderecos.Add(p);
                }
                return enderecos;
            }
            catch (SqlException sqlex)
            {
                return null;
            }
        }

        public bool delete(Endereco e)
        {
            try
            {
                SqlCommand command = new SqlCommand("delete from endereco where id_cliente = " + e.Id_cliente + " and endereco = '"+e.ENdereco+"' and numero = "+e.Numero+" ", this.conn);
                command.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqlex)
            {
                return false;
            }
        }

        public void openConnection()
        {
            this.conn.Open();
        }

        public void closeConnection()
        {
            this.conn.Close();
        }

        public void putConnection(SqlConnection connection)
        {
            this.conn = connection;
        }
    }
}
